﻿<%@ Page Language="VB" Debug="true" %>
<%@ Register TagPrefix="wmx" Namespace="Microsoft.Matrix.Framework.Web.UI" Assembly="Microsoft.Matrix.Framework, Version=0.6.0.0, Culture=neutral, PublicKeyToken=6f763c9966660626" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<%@ import Namespace="System.Net.Mail" %>
<%@ import Namespace="System.Net" %>
<script runat="server">

    Sub Enviar(texto as string, email as string, numero as integer)
        Dim Message As MailMessage = New MailMessage()
        Message.To.Add(email)
        Message.From = New MailAddress("ZT Representações Comerciais LTDA.<ztrep2@terra.com.br>")
        Message.Subject = "Pedido: " & numero
        Message.Body = texto
	Message.IsBodyHtml = true
        try
            Dim smtp As SmtpClient = New SmtpClient("smtp.rio.terra.com.br")
            smtp.Credentials = New NetworkCredential("ztrep2", "shalom")
            smtp.Send(Message)
            Painel.Text = Painel.Text & "Pedido " & numero & " enviado.<br>"
        catch
            Painel.Text = Painel.Text & "<font color='red'><b>Erro no pedido " & numero & "</b></font><br>"
        end try
    end sub
    
    Sub Page_Load()
        Dim ConnectionString As String = ConfigurationSettings.AppSettings("strcnn")
        Dim myConnection As New OleDbConnection(ConnectionString)
        Dim myConnection2 As New OleDbConnection(ConnectionString)
        Dim myCommand as New OleDbCommand
        Dim myCommand2 as New OleDbCommand
        Dim myDataReader as OleDbDataReader
        Dim myDataReader2 as OleDbDataReader
        Dim texto as String
        Dim email as string
        Dim numero as integer
    
        myConnection.Open()
        myConnection2.Open()
    
        myCommand.Connection = myConnection
        myCommand2.Connection = myConnection2
    
       myCommand.CommandText = Request.Querystring("strsql")
        myDataReader = myCommand.ExecuteReader()
    
        while myDataReader.Read()
            email = myDataReader("fabrica.email")
            if LCase(myDataReader("fabrica.razaosocial")) like "*jolitex*" then
                'padrao jolitex
                numero = myDataReader("Pedido.codigojolitex")
                Dim Desc
                Try
                    Desc = myDataReader("Desconto") / 100
                Catch
                    Desc = 0
                End Try
                texto = "<html><head><link href='http://zt.no-ip.com/wwwroot/imprimir.css' type='text/css' rel='stylesheet' /></head><body leftmargin='0' topmargin='0' arial='arial'><table cellspacing='0' cellpadding='0' width='933' border='0'><tr><td colspan='3'><table height='83' cellspacing='0' cellpadding='0' width='933' border='0'><tr><td width='72' height='75'><img height='75' src='http://zt.no-ip.com/wwwroot/urso.jpg' width='72' /></td><td align='middle' width='619'></td></tr></table></td></tr><tr><td><table cellspacing='0' cellpadding='0' width='933' border='0'><tr><td width='15%'><font size='3'><b>Código do cliente:" & myDataReader("cliente.codigojolitex") & "</b></font></td>"
                texto = texto & "<td width='60%'><font size='3'><b>Razão social: " & myDataReader("cliente.razaosocial") & "</b></font></td><td width='17%'>ZT Representações Comerciais</td><td width='8%'>Região: " & myDataReader("regiao") & "</td></tr></table></td></tr><tr><td colspan='3'><table cellspacing='0' cellpadding='0' width='933' border='0'><tr><td width='50%'>Endereço: " & myDataReader("endereco") & "</td><td width='15%'>"
                texto = texto & "Bairro: " & myDataReader("bairro") & "</td><td width='15%'>CEP: " & myDataReader("cep") & "</td><td width='15%'>Cidade: " & myDataReader("cidade") & "</td><td width='5%'>UF: " & myDataReader("estado") & "</td></tr></table></td></tr><tr><td colspan='3'><table cellspacing='0' cellpadding='0' width='933' border='0'><tr><td width='50%'>CNPJ: " & myDataReader("cnpj") & "</td><td width='30%'>Inscrição estadual: " & myDataReader("inscricaoestadual") & "</td><td width='20%'>Telefone: " & myDataReader("telefone") & "</td></tr></table></td></tr><tr><td colspan='3'><table cellspacing='0' cellpadding='0' width='933' border='0'>"
                texto = texto & "<tr><td width='50%'>Endereço entrega: " & myDataReader("enderecoentrega") & "</td><td width='15%'>Bairro: " & myDataReader("bairroentrega") & "</td><td width='15%'>CEP: " & myDataReader("cepentrega") & "</td><td width='15%'>Cidade: " & myDataReader("cidadeentrega") & "</td><td width='5%'>UF: " & myDataReader("estadoentrega") & "</td></tr></table></td></tr><tr><td colspan='3'><table cellspacing='0' cellpadding='0' width='933' border='0'><tr><td width='50%'>Endereço cobrança: " & myDataReader("enderecocobranca") & "</td><td width='15%'>Bairro: " & myDataReader("bairrocobranca") & "</td><td width='15%'>CEP: " & myDataReader("cepcobranca") & "</td><td width='15%'>Cidade: " & myDataReader("cidadecobranca") & "</td><td width='5%'>UF: " & myDataReader("estadocobranca") & "</td></tr></table></td></tr><tr><td colspan='3'><table cellspacing='0' cellpadding='0' width='933' border='0'><tr></tr></table></td></tr><tr><td colspan='3'><table cellspacing='0' cellpadding='0' width='933' border='0'><tr><td width='50%'>CNPJ cobrança: " & myDataReader("CNPJcobranca") & "</td><td width='30%'>Inscrição estadual cobrança: " & myDataReader("inscricaoestadualcobranca") & "</td><td width='20%'>Telefone cobrança: " & myDataReader("telefonecobranca") & "</td></tr></table></td></tr><tr><td colspan='3'><table cellspacing='0' cellpadding='0' width='933' border='0'>"
                texto = texto & "<tr><td width='65%'></td><td valign='top' width='35%'>Transportadora: " & myDataReader("transportadora.razaosocial") & "</td></tr></table></td></tr><tr><td colspan='3'><table height='50' cellspacing='0' cellpadding='0' width='933' border='0'><tr><td width='30%' height='50'><table bordercolor='#000000' cellspacing='0' cellpadding='1' border='1'><tr align='middle' bgcolor='#cccccc'><td>"
                texto = texto & "<strong>Data do pedido </strong></td><td><strong>Número do pedido </strong></td></tr><tr align='middle'><td><font size='3'><strong>" & myDataReader("data") & "</strong></font></td><td><font size='5'><strong>" & myDataReader("pedido.codigojolitex") & "</strong></font></td></tr></table></td><td width='20%'></td><td width='30%'>Condição de pagamento: " & myDataReader("condicaopagamento") & "</td><td width='20%'>Data de entrega: " & myDataReader("prazoentrega") & "</td>"
                texto = texto & "</tr></table></td></tr><tr><td colspan='3'><br /><table cellspacing='0' cellpadding='2' rules='all' bordercolor='Black' border='1' id='Tabela' style='border-color:Black;border-width:1px;border-style:solid;width:100%;border-collapse:collapse;'><tr style='font-weight:bold;'><td style='background-color:#CCCCCC;'>Código</td><td style='background-color:#CCCCCC;'>Descrição</td><td colspan='2' style='background-color:#CCCCCC;' align='right'>Quantidade</td><td style='background-color:#CCCCCC;' align='right'>Preço unitário</td><td style='background-color:#CCCCCC;' align='right'>Total</td></tr>"
    
                myCommand2.CommandText = "select * from produto, pedido where pedido.id=produto.idpedido and codigojolitex=" & myDataReader("pedido.codigojolitex")
                myDataReader2 = myCommand2.ExecuteReader()
                while myDataReader2.Read()
                    texto = texto & "<tr><td>" & myDataReader2("codigo") & "</td><td>" & myDataReader2("descricao") & "</td><td colspan='2' align='right'>" & myDataReader2("quantidade") & "</td><td align='right'>R$ " & Format(myDataReader2("precounitario"), "#.00") & "</td><td align='right'>R$ " & Format(myDataReader2("quantidade") * myDataReader2("precounitario"), "#.00") & "</td></tr>"
                end while
                myDataReader2.Close()
    
                myCommand2.CommandText = "select sum(quantidade*precounitario) as total from pedido, produto where pedido.id=produto.idpedido and codigojolitex=" & myDataReader("pedido.codigojolitex")
                myDataReader2 = myCommand2.ExecuteReader()
                myDataReader2.Read()
                Dim ValMerc = myDataReader2("Total")
                myDataReader2.Close()
                Dim ValDesc = ValMerc * Desc
                Dim ValorDesconto = Format(ValDesc, "#0.00")
                myDataReader2.Close()
    
                myCommand2.CommandText = "select sum(" & Replace(1-Desc, ",", ".") & "*quantidade*precounitario*iif(descricao like '%tap%' or descricao like '%banh%', 0.1, 0)) as total from pedido, produto where pedido.id=produto.idpedido and codigojolitex=" & myDataReader("pedido.codigojolitex")
                myDataReader2 = myCommand2.ExecuteReader()
                myDataReader2.Read()
                Dim ValIpi = myDataReader2("Total")
                myDataReader2.Close()
                Dim ValorIpi = iif(ValIpi=0, "0,00", Format(ValIpi, "#.00"))
                Dim ValorMerc = Format(ValMerc, "#.00")
                Dim TotalMerc = ValMerc + ValIpi - ValDesc
                Dim TotalMercadoria = Format(TotalMerc, "#.00")
    
    
                texto = texto & "<tr><td height='3' colspan='6'></td></tr>"
                texto = texto & "<tr><td colspan='2'></td><td bgcolor='#cccccc' width='125' height='1' valign='middle'>Quantidade de produtos</td><td valign='middle' align='center'>" & myDataReader("pecas") & "</td><td colspan='2' rowspan='4'>"
    
                texto = texto & "<table align='right' bordercolor='#000000' cellspacing='0' cellpadding='4' border='1' width='200'>"
                texto = texto & "<tr align='middle'><td bgcolor='#cccccc'><strong><b>Total da mercadoria</b></strong></td><td align='right'>R$ " & ValorMerc & "</td></tr>"
                texto = texto & "<tr align='middle'><td bgcolor='#cccccc'><strong><b>Desconto " & Desc*100 & "%</b></strong></td><td align='right'>R$ " & ValDesc & "</td></tr>"
                texto = texto & "<tr align='middle'><td bgcolor='#cccccc'><strong><b>Ipi 10%</b></strong></td><td align='right'>R$ " & ValorIpi & "</td></tr>"
                texto = texto & "<tr align='middle'><td bgcolor='#cccccc'><strong><b>Valor total líquido</b></strong></td><td align='right'>R$ " & TotalMercadoria & "</td></tr></table></td></tr>"
                texto = texto & "<tr><td colspan='4' rowspan='3'>Observações: " & myDataReader("pedido.observacao") & "</td></tr></table>"
    
                texto = texto & "</td></tr><tr><td colspan='3'><br /><table width='100%'><tr><td><table bordercolor='#000000' cellspacing='0' cellpadding='1' width='70%' border='1'><tr align='middle' bgcolor='#ffffff'><td><strong>Crédito</strong></td><td><strong>Vendas</strong></td><td><strong>Recepção</strong></td><td><strong>Digitação</strong></td></tr><tr valign='bottom' align='middle'><td>"
                texto = texto & "<br /><br />Data / Visto</td><td>Data / Visto</td><td>Data / Visto</td><td>Data / Visto</td></tr></table></td><td><table bordercolor='#000000' cellspacing='0' cellpadding='1' border='1'><tr align='middle' bgcolor='#cccccc'><td><strong>Data do pedido </strong></td><td><strong>Número do pedido </strong></td></tr><tr align='middle'><td><font size='3'><strong>" & Format(myDataReader("data"), "dd/MM/yy") & "</strong></font></td><td><font size='5'><strong>" & myDataReader("pedido.codigojolitex") & "</strong></font></td></tr>"
                texto = texto & "</table></td></tr></table></td></tr></table></body></html>"
            else
                'padrao generico
                numero = myDataReader("Pedido.id")
                texto = "<table bordercolor='#000000' cellspacing='5' cellpadding='10' width='700' border='1'><tr><td><table cellspacing='0' cellpadding='0' width='100%' border='0'><tr><td align='middle'><strong><font size='4'>ZT Representações<br>Comerciais Ltda.</font></strong></td><td valign='center' align='middle' width='291'>Rua República do Líbano 61/1112<br>Centro - 20061-030 - Rio de Janeiro - RJ<br>Tel: (21) 2242-7635 / 2252-9529<br>Fax: 2252-2852<br>e-mail: ztjolit@bordallo.com.br</td><td valign='top' align='middle'>Pedido<br>N° " & myDataReader("Pedido.ID") & "</td></tr></table></td></tr><tr><td><table cellspacing='0' cellpadding='0' width='100%' border='0'><tr><td>" & myDataReader("Fabrica.RazaoSocial") & "</td>"
                texto = texto & "<td align='right'>" & Format(myDataReader("Data"), "dd/MM/yy") & "</td></tr></table></td></tr><tr><td><table cellspacing='0' cellpadding='2' width='100%' border='0'><tr><td>Cliente:</td><td colspan='3'>" & myDataReader("Cliente.RazaoSocial") & "</td><td>Telefone:</td><td>" & myDataReader("Telefone") & "</td></tr><tr><td>Endereço:</td><td colspan='3'>" & myDataReader("Endereco") & "</td><td>Bairro:</td><td>" & myDataReader("Bairro") & "</td></tr><tr><td>CEP:</td><td>" & myDataReader("CEP") & "</td><td>Cidade:</td><td>" & myDataReader("Cidade") & "</td><td>Estado:</td><td>" & myDataReader("Estado") & "</td></tr><tr><td>CNPJ:</td><td colspan='3'>" & myDataReader("CNPJ") & "</td><td>Inscr. est.:</td>"
                texto = texto & "<td>" & myDataReader("InscricaoEstadual") & "</td></tr></table></td></tr><tr><td><table cellspacing='0' cellpadding='2' width='100%' border='0'><tr><td>End. p/ entrega:</td><td>" & myDataReader("EnderecoEntrega") & "</td><td>Prazo de entrega:</td><td>" & myDataReader("PrazoEntrega") & "</td></tr><tr><td>Endereço p/ cobrança:</td><td>" & myDataReader("EnderecoCobranca") & "</td><td>Condição de pagamento:</td><td>" & myDataReader("CondicaoPagamento") & "</td></tr><tr><td>Transportadora:</td><td>" & myDataReader("Transportadora.RazaoSocial") & "</td><td>Vendedor:</td><td>" & myDataReader("Sigla") & "</td></tr></table></td></tr><tr><td>"
                texto = texto & "<table cellspacing='0' cellpadding='2' rules='all' bordercolor='Black' border='1' style='border-color:Black;border-width:1px;border-style:solid;width:100%;border-collapse:collapse;'><tr style='font-weight:bold;'><td>Quantidade</td><td>Código</td><td>Descrição</td><td>Preço unitário</td></tr>"
    
                dim total = Format(myDataReader("Total"), "#.00")
                dim obs = Replace(myDataReader("Pedido.Observacao"), Chr(13), "<br>")
    
                myCommand2.CommandText = "select produto.*, quantidade*precounitario as total from produto where idpedido=" & myDataReader("Pedido.ID")
                myDataReader2 = myCommand2.ExecuteReader()
                while myDataReader2.Read()
                    texto = texto & "<tr><td>" & myDataReader2("Quantidade") & "</td><td>" & myDataReader2("Codigo") & "</td><td>" & myDataReader2("Descricao") & "</td><td>" & Format(myDataReader2("PrecoUnitario"), "#.00") & "</td></tr>"
                end while
                myDataReader2.Close()
                texto = texto & "</table></td></tr><tr><td align='right'><table cellspacing='0' cellpadding='2' border='0'><tr><td>Total:</td><td>R$ " & total & "</td></tr></table></td></tr><tr><td><table cellspacing='0' cellpadding='2' width='100%' border='0'><tr><td>Observações:</td></tr><tr><td>" & obs & "</td></tr></table></td></tr></table>"
            end if
            Enviar(texto, email, numero)
        end while
    
        myDataReader.Close()
    End Sub

</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
    <table bordercolor="navajowhite" cellspacing="0" cellpadding="2" width="950" align="center" bgcolor="floralwhite">
        <tbody>
            <tr>
                <td valign="center" align="middle" bgcolor="#ffdead" colspan="2">
                    <h2>Enviar pedidos por e-mail 
                    </h2>
                </td>
            </tr>
            <tr>
                <td valign="top">
                    <asp:Label id="Painel" runat="server"></asp:Label></td>
            </tr>
        </tbody>
    </table>
</body>
</html>
